package com.dldata.devtools30.utils.dhtmlX;

import com.dldata.devtools30.utils.StringUtil;
import com.dldata.devtools30.utils.Util;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;

import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by 吴岳峰 on 2018-11-16.
 */
@Slf4j
public class ExcelWriterXss {

    private XSSFWorkbook wb;
    private XSSFSheet sheet;
    private ExcelColumn[][] cols;
    private int colsNumber = 0;
    private ExcelXmlParser parser;

    public int headerOffset = 0;
    public int scale = 32;
    public String pathToImgs = "";//optional, physical path
    public int fontSize = 10;

    String bgColor = "";
    String lineColor = "";
    String headerTextColor = "";
    String scaleOneColor = "";
    String scaleTwoColor = "";
    String gridTextColor = "";
    String watermarkTextColor = "";

    private int cols_stat;
    private int rows_stat;
    Map<String,XSSFCellStyle> styleMap;
    private String watermark = null;

    public void generate(String xml, String sheetname) throws Exception{
        parser = new ExcelXmlParser();
        parser.setXML(xml);
        createExcel(sheetname);
        setColorProfile();
        createStyle();
        headerPrint(parser);
        rowsPrint(parser);
//        footerPrint(parser);
//        insertHeader(parser);
//        insertFooter(parser);
//        watermarkPrint(parser);
    }

    private void createExcel(String sheetname) throws IOException {
		/* Save generated excel to file.
		 * Can be useful for debug output.
		 * */
		/*
		FileOutputStream fos = new FileOutputStream("d:/test.xls");
		wb = Workbook.createWorkbook(fos);
		*/
        wb = new XSSFWorkbook();
        sheet = wb.createSheet(StringUtil.isEmptyOrLength0(sheetname)?"Sheet":sheetname);
        styleMap = new HashMap<>();
    }

    public void outputExcel(HttpServletResponse resp, String title,OutputStream outputStream) throws IOException {
        resp.setContentType("application/vnd.ms-excel");
        resp.setCharacterEncoding("UTF-8");
        String downTitle = StringUtil.isEmptyOrLength0(title)?"grid.xls":title+".xls";
        downTitle= URLEncoder.encode(downTitle, "UTF-8");
        resp.setHeader("Content-Disposition", "attachment;filename="+downTitle);
        resp.setHeader("Cache-Control", "max-age=0");
        wb.write(outputStream);
//        wb.close();
    }

//    public void close()throws Exception{
//        wb.write();
//        wb.close();
//    }

    private void headerPrint(ExcelXmlParser parser) throws IOException {
        cols = parser.getColumnsInfo("head");

        int widths[] = parser.getWidths();
        this.cols_stat = widths.length;

        int sumWidth = 0;
        for (int i = 0; i < widths.length; i++) {
            sumWidth += widths[i];
        }



        //冻结第表头
        sheet.createFreezePane( 0, cols.length-1, 0,cols.length-1 );

        XSSFRow row = sheet.createRow((int) 0);

        headerOffset = cols.length-1;

        if (parser.getWithoutHeader() == false) {
            for (int i = 0; i < cols.length-1; i++) {
                row = sheet.createRow((int) i);
                row.setHeight((short)450);
                for (int j = 0; j < cols[i].length; j++) {

                    sheet.setColumnWidth(j,widths[j]*scale);
                    int colspan = cols[i][j].getColspan()==0?1:cols[i][j].getColspan();
                    int rowspan = cols[i][j].getRowspan()==0?1:cols[i][j].getRowspan();

                    // 设置表头

                    XSSFCell cell = row.createCell(j);
                    //合并单元格
                    String name = cols[i][j].getName();
                    if(!StringUtil.isEmptyOrLength0(name)) {
                        CellRangeAddress region = new CellRangeAddress(i, i + rowspan - 1, j, j + colspan - 1);
                        if(region.getNumberOfCells()>1){
                            sheet.addMergedRegion(region);
                        }
                        CTCell ctCell= cell.getCTCell();
                        ctCell .setV(name.replace("&lt;", "<").replace("&gt;", ">"));
                        cell.setCTCell(ctCell);
                        cell.setCellStyle(styleMap.get("t"+j));
                    }
                }
            }
        }

    }

    /*private void footerPrint(ExcelXmlParser parser) throws IOException {//表头表底
        cols = parser.getColumnsInfo("foot");
        if (cols == null) return;
        if (parser.getWithoutHeader() == false) {
            for (int i = 0; i < cols.length; i++) {
                XSSFRow row = sheet.createRow((int) i + headerOffset);
                row.setHeight((short)450);
                for (int j = 0; j < cols[i].length; j++) {
                    XSSFFont font =  wb.createFont();//XSSFFont.ARIAL, fontSize, XSSFFont.BOLD
                    font.setColor(colors.getColor(headerTextColor));
                    WritableCellFormat f = new WritableCellFormat (font);
                    f.setBackground(colors.getColor(bgColor, wb));
                    f.setBorder(Border.ALL, BorderLineStyle.THIN, colors.getColor(lineColor, wb));
                    f.setVerticalAlignment(VerticalAlignment.CENTRE);

                    f.setAlignment(Alignment.CENTRE);
                    String name = cols[i][j].getName();
                    Label label = new Label(j, i + headerOffset, name, f);
                    sheet.addCell(label);
                }
            }
            for (int i = 0; i < cols.length; i++) {
                for (int j = 0; j < cols[i].length; j++) {
                    int cspan = cols[i][j].getColspan();
                    if (cspan > 0) {
                        sheet.mergeCells(j, headerOffset + i, j + cspan - 1, headerOffset + i);
                    }
                    int rspan = cols[i][j].getRowspan();
                    if (rspan > 0) {
                        sheet.mergeCells(j, headerOffset + i, j, headerOffset + i + rspan - 1);
                    }
                }
            }
        }
        headerOffset += cols.length;
    }

    private void watermarkPrint(ExcelXmlParser parser) throws WriteException {//水印
        if (watermark == null) return;

        WritableFont font = new WritableFont(WritableFont.ARIAL, fontSize, WritableFont.BOLD);
        font.setColour(colors.getColor(watermarkTextColor, wb));
        WritableCellFormat f = new WritableCellFormat (font);
        f.setBorder(Border.ALL, BorderLineStyle.THIN, colors.getColor(lineColor, wb));
        f.setVerticalAlignment(VerticalAlignment.CENTRE);

        f.setAlignment(Alignment.CENTRE);
        Label label = new Label(0, headerOffset, watermark , f);
        sheet.addCell(label);
        sheet.mergeCells(0, headerOffset, colsNumber, headerOffset);
    }*/

    private void rowsPrint(ExcelXmlParser parser) throws IOException {
        ExcelRow[] rows = parser.getGridContent();
        if (rows == null) return;
        this.rows_stat = rows.length;
        XSSFRow row = sheet.createRow((int)headerOffset);


        for(int i=0;i<rows.length;i++){
//            if(i%1000==0){
//                log.info("Excel Number : "+i);
//            }
            ExcelCell[] cells = rows[i].getCells();
            row = sheet.createRow(i + headerOffset);
            row.setHeight((short)400);
            for(int j=0;j<cells.length;j++){

                XSSFCell cell = row.createCell(j);

                String rowData = Util.checkNotNull(cells[j].getValue())?cells[j].getValue():"";
                Pattern p_html=Pattern.compile("<[^>]+>",Pattern.CASE_INSENSITIVE);
                Matcher m_html=p_html.matcher(rowData);
                rowData=m_html.replaceAll("");

                CTCell ctCell= cell.getCTCell();
                ctCell .setV(rowData);
                cell.setCTCell(ctCell);

                cell.setCellStyle(styleMap.get("c"+i%2+j));
            }
        }
        headerOffset += rows.length;
    }

    /*private void insertHeader(ExcelXmlParser parser) throws IOException, RowsExceededException {
        if (parser.getHeader() == true) {
            sheet.insertRow(0);
            sheet.setRowView(0, 5000);
            File imgFile = new File(pathToImgs + "/header.png");
            WritableImage img = new WritableImage(0, 0, cols[0].length, 1, imgFile);
            sheet.addImage(img);
            headerOffset++;
        }
    }

    private void insertFooter(ExcelXmlParser parser) throws IOException{
        if (parser.getFooter() == true) {
            sheet.setRowView(headerOffset, 5000);
            File imgFile = new File(pathToImgs + "/footer.png");
            WritableImage img = new WritableImage(0, headerOffset, cols[0].length, 1, imgFile);
            sheet.addImage(img);
        }
    }*/

    public int getColsStat() {
        return this.cols_stat;
    }

    public int getRowsStat() {
        return this.rows_stat;
    }

    private void setColorProfile() {
        String profile = parser.getProfile();
        if ((profile.equalsIgnoreCase("color"))||profile.equalsIgnoreCase("full_color")) {
            bgColor = "D1E5FE";
            lineColor = "A4BED4";
            headerTextColor = "000000";
            scaleOneColor = "FFFFFF";
            scaleTwoColor = "E3EFFF";
            gridTextColor = "000000";
            watermarkTextColor = "8b8b8b";
        } else {
            if (profile.equalsIgnoreCase("gray")) {
                bgColor = "E3E3E3";
                lineColor = "B8B8B8";
                headerTextColor = "000000";
                scaleOneColor = "FFFFFF";
                scaleTwoColor = "EDEDED";
                gridTextColor = "000000";
                watermarkTextColor = "8b8b8b";
            } else {
                bgColor = "FFFFFF";
                lineColor = "000000";
                headerTextColor = "000000";
                scaleOneColor = "FFFFFF";
                scaleTwoColor = "FFFFFF";
                gridTextColor = "000000";
                watermarkTextColor = "000000";
            }
        }
    }
    public void createStyle(){
        cols = parser.getColumnsInfo("head");
        int widths[] = parser.getWidths();

        for (int j = 0; j < cols[0].length; j++) {
            XSSFCellStyle style = wb.createCellStyle();
            XSSFDataFormat format = wb.createDataFormat();
            XSSFColor colors = new XSSFColor();

            style.setDataFormat(format.getFormat("@"));
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

            sheet.setColumnWidth(j,widths[j]*scale);

            // 设置表头
            XSSFFont font = wb.createFont();
            font.setFontName("宋体");

            font.setBold(true);
            colors.setARGBHex(headerTextColor);
            font.setColor(colors);
            style.setFont(font);
            colors.setARGBHex(bgColor);
            style.setFillForegroundColor(colors);
            style.setFillPattern(FillPatternType.ALT_BARS);

//            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
//            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
//            style.setBorderTop(XSSFCellStyle.BORDER_THIN);

            style.setBorderBottom(BorderStyle.DASH_DOT);
            style.setBorderLeft(BorderStyle.DASH_DOT);
            style.setBorderRight(BorderStyle.DASH_DOT);
            style.setBorderTop(BorderStyle.DASH_DOT);

            colors.setARGBHex(lineColor);
            style.setTopBorderColor(colors);//上边框颜色
            style.setBottomBorderColor(colors);//下边框颜色
            style.setLeftBorderColor(colors);//左边框颜色
            style.setRightBorderColor(colors);//右边框颜色
            //合并单元格

            styleMap.put("t"+j,style);
        }

        ExcelRow[] rows = parser.getGridContent();

        ExcelCell[] cells = rows[0].getCells();
        for(int i=0;i<2;i++){
            for(int j=0;j<cells.length;j++) {
                XSSFCellStyle style = wb.createCellStyle();
                XSSFDataFormat format = wb.createDataFormat();
                XSSFColor colors = new XSSFColor();

                style.setDataFormat(format.getFormat("@"));
               // style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
                style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
                String al = cells[j].getAlign();
                if (al == "")
                    al = cols[0][j].getAlign();
                if (al.equalsIgnoreCase("left")) {
                  //  style.setAlignment(XSSFCellStyle.ALIGN_LEFT);//水平居左
                    style.setAlignment(HorizontalAlignment.LEFT);//水平居左
                } else if (al.equalsIgnoreCase("right")) {
                  //  style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);//水平居右
                    style.setAlignment(HorizontalAlignment.RIGHT);//水平居右
                } else {
                  //  style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
                    style.setAlignment(HorizontalAlignment.CENTER);//水平居中
                }

                if (i % 2 == 1) {
                    colors.setARGBHex(scaleTwoColor);
                    style.setFillForegroundColor(colors);
                } else {
                    colors.setARGBHex(scaleOneColor);
                    style.setFillForegroundColor(colors);
                }
//                style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//                style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
//                style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
//                style.setBorderRight(XSSFCellStyle.BORDER_THIN);
//                style.setBorderTop(XSSFCellStyle.BORDER_THIN);

                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                style.setBorderBottom(BorderStyle.DASH_DOT);
                style.setBorderLeft(BorderStyle.DASH_DOT);
                style.setBorderRight(BorderStyle.DASH_DOT);
                style.setBorderTop(BorderStyle.DASH_DOT);

                colors.setARGBHex(lineColor);
                style.setTopBorderColor(colors);//上边框颜色
                style.setBottomBorderColor(colors);//下边框颜色
                style.setLeftBorderColor(colors);//左边框颜色
                style.setRightBorderColor(colors);//右边框颜色

                styleMap.put("c"+i+j,style);
            }
        }
    }

    public void setWatermark(String mark) {
        watermark = mark;
    }

    public void setFontSize(int fontsize) {
        this.fontSize = fontsize;
    }

    public XSSFSheet getSheet() {
        return sheet;
    }

    public XSSFWorkbook getWb() {
        return wb;
    }
}
